select distinct a.let_id codice_lettura,a.mis_id,sog.sog_id,
b.CUF_ID,c.ATT_IDEXT PDR,concat(concat(c.con_ANNO,'/'),c.CON_ID) contratto,
mis.mis_pos,
trunc(a.ver_dtini) ver_dtini,
trunc(a.ver_dtfine) ver_dtfine,a.lmis_val lettura,
bo_lettura.GET_VAL_LETTURA_PRECEDENTE (
             a.AZ_ID,
             (SELECT L.ATTGMIS_ID
                FROM LETTURA L
               WHERE L.AZ_ID = a.AZ_ID AND L.LET_ID = a.LET_ID),
             MIS.MIS_ID,
             (SELECT L.VER_DTINI
                FROM LETTURA L
               WHERE L.AZ_ID = a.AZ_ID AND L.LET_ID = a.LET_ID)) lettura_precedente,
a.lmis_valcon,
TSM.FACO_ID,
(SELECT F.FACO_DES
             FROM FASCON F
            WHERE     F.AZ_ID = TSM.AZ_ID
                  AND F.RFACO_ID = TSM.RFACO_ID
                  AND F.FACO_ID = TSM.FACO_ID)
             FACO_DES,
a.lmis_umidval "U/M",
st.SMIS_MAT Matricola,
(SELECT TSM_DES
             FROM TSTRMIS B
            WHERE st.AZ_ID = B.AZ_ID AND st.TSM_ID = B.TSM_ID)
              descrizione_ctr,
a.lmis_flpaszero giro_cont,
b.JOB_ID,

(select distinct trunc(JOB_DTREG) from jobqueue  where 
 az_id='01' and job_id=b.job_id) job_dtreg
from letmisval a, 
     lettura b,
     UE_ASP_V_conubifrn c,    
    ragfat rag,
    MISURA MIS,
    TCMISTSM TSM,
    GMISSMIS GM,     
    strmis st,
     contratto con,
     SOGGETTO SOG
where a.LET_ID= b.LET_ID
and a.az_id='01'
and b.az_id='01'
and c.az_id='01'
AND SOG.AZ_ID='01'
and con.az_id='01'
and rag.az_id='01'
and MIS.AZ_ID='01'
AND GM.AZ_ID = MIS.AZ_ID
and St.az_ID = GM.az_ID
AND GM.GMSM_NUM = MIS.GMSM_NUM
AND MIS.MIS_ID = a.MIS_ID
and St.SMIS_ID = GM.SMIS_ID
AND TSM.AZ_ID = MIS.AZ_ID
AND TSM.TCMIS_ID = MIS.TCMIS_ID
and st.az_id='01'
and b.CUF_ID = c.CUF_ID
and c.SER_ID ='03'
and c.STA_IDSYS =2
and c.CON_ID = con.CON_ID
and c.CON_ANNO = con.CON_ANNO
AND CON.SOG_ID=SOG.SOG_ID
and con.RAGF_ID = rag.RAGF_ID
and 
      (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = rag.AZ_ID
          AND MASTER_ISTA.CSI_ID = rag.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = rag.ISTA_VERSTATO
           ) in ( 2,31,23)
and 
/*Stato Lettura start*/
(
      (select CASE
              WHEN     ista.ISS_STAID <> 0
                   AND ista.ISS_STAIDSUPER = 0
                   AND ista.ISS_SSTAIDSUPER = 0
              THEN
                 ista.ISS_STAID
              WHEN     ista.ISS_STAID <> 0
                   AND ista.ISS_STAIDSUPER <> 0
                   AND ista.ISS_SSTAIDSUPER = 0
              THEN
                 ista.ISS_STAIDSUPER
              WHEN     ista.ISS_STAID <> 0
                   AND ista.ISS_STAIDSUPER <> 0
                   AND ista.ISS_SSTAIDSUPER <> 0
              THEN
                 ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista  where 
            ISTA.AZ_ID = b.AZ_ID
          AND ISTA.CSI_ID = b.CSI_ID
          AND ISTA.ISTA_VERSTATO = b.ISTA_VERSTATO
           ) <  case when ^STATO LETTURA(999 = TUTTI-2 Acquisito-10 Da fatturare-11 Fatturato-17 Da Rettificare-18 Rettificato-27 Da Rifatturare xx,xx,xx
		   );A;ALFABETICO^^ like '%999%' then 99 else 0 end
or 
      (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = b.AZ_ID
          AND MASTER_ISTA.CSI_ID = b.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = b.ISTA_VERSTATO
           )  IN ( ^STATO LETTURA (999 = TUTTI-2 Acquisito-10 Da fatturare-11 Fatturato-17 Da Rettificare-18 Rettificato
		   -27 Da Rifatturare xx,xx,xx);N;NUMERICO;1^^ ))

           
/*Stato Lettura end*/

and (st.VER_DTFINE=to_date('31129999','ddmmyyyy')
OR ST.VER_DTFINE>=B.VER_DTFINE)
--AND A.LET_ID=1747355
/*Start controllo data lettura*/
and a.VER_DTFINE>=to_date(^DATA FINE LETTURA >= DI DD/MM/YYYY;A;ALFABETICO^^,'dd/mm/yyyy')
and a.VER_DTFINE<=to_date(^DATA FINE LETTURA <= DI DD/MM/YYYY;A;ALFABETICO^^,'dd/mm/yyyy')
/*End controllo data lettura*/
/*Start controllo soggetto id*/
and ((SOG.SOG_ID< case when ^ID SOGGETTO (999999 = TUTTI) xx,xx,xx;N;NUMERICO^^ like '%999999%' then 999999 else 0 end)
OR (SOG.SOG_ID IN (^ID SOGGETTO (999999 = TUTTI) xx,xx,xx;N;NUMERICO;4^^)))
/*End controllo soggetto id*/
order by c.ATT_IDEXT
